package com.grandprixsw.evmsmobile;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
public class DBHandler extends SQLiteOpenHelper {
// on curtis's branch2
 
    // All Static variables
    // Database Version
    private static final int DB_VERSION = 4;
 
    // Database name
    private static final String DB_NAME = "EVMS_DB";
 
    // Projects table name
    private static final String TABLE_PROJECTS = "projects";
 
    // Projects table columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "project_name";
    private static final String COL_BCWS = "bcws";
    private static final String COL_BCWP = "bcwp";
    private static final String COL_ACWP = "acwp";
    private static final String COL_MR = "mr";
    private static final String COL_EAC = "eac";
    private static final String COL_BAC = "bac";
    private static final String COL_CBB = "cbb";
    
    public DBHandler(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
 
    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_PROJECTS_TABLE = "CREATE TABLE " + TABLE_PROJECTS + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
        		+ KEY_NAME + " TEXT NOT NULL,"
                + COL_BCWS + " REAL," 
                + COL_BCWP + " REAL," 
                + COL_ACWP + " REAL," 
                + COL_MR + " REAL," 
                + COL_EAC + " REAL," 
                + COL_BAC + " REAL," 
                + COL_CBB + " REAL" 
        		+ ")";
        
        db.execSQL(CREATE_PROJECTS_TABLE);
    }
 
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROJECTS);
 
        // Create tables again
        onCreate(db);
    }
 
    /**
     * CRUD Operations
     */
 
    // Adding new Project
    void addProject(EVMSProject pProject) {
        SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put(KEY_NAME, pProject.getName());
        values.put(COL_BCWS, pProject.getBCWS());
        values.put(COL_BCWP, pProject.getBCWP());
        values.put(COL_ACWP, pProject.getACWP());
        values.put(COL_MR, pProject.getMR());
        values.put(COL_EAC, pProject.getEAC());
        values.put(COL_BAC, pProject.getBAC());
        values.put(COL_CBB, pProject.getCBB());

System.out.println("Inside DBHandler:addProject..."); 
System.out.println("values = " + values.toString()); 

// Inserting Row
        db.insert(TABLE_PROJECTS, null, values);
        db.close(); // Close database connection
    }
 
    // Retrieving a single Project by ID
    EVMSProject getProject(int pID) {
    	
        SQLiteDatabase db = this.getReadableDatabase();
 
        Cursor cursor = db.query(TABLE_PROJECTS, new String[] { KEY_ID,
                KEY_NAME, COL_BCWS, COL_BCWP, COL_ACWP, COL_MR, COL_EAC, 
                COL_BAC, COL_CBB }, KEY_ID + "=?",
                new String[] { String.valueOf(pID) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();
 
        EVMSProject project = new EVMSProject(Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), Float.parseFloat(cursor.getString(2)), Float.parseFloat(cursor.getString(3)),
                Float.parseFloat(cursor.getString(4)), Float.parseFloat(cursor.getString(5)),
                Float.parseFloat(cursor.getString(6)), Float.parseFloat(cursor.getString(7)),
                Float.parseFloat(cursor.getString(8)));
        
        return project;
    }

    // Retrieving a single Project by Name
    EVMSProject getProject(String pName) {

 System.out.println("Inside DBHandler:getProject(by Name)...");    
        SQLiteDatabase db = this.getReadableDatabase();
 
        Cursor cursor = db.query(TABLE_PROJECTS, new String[] { KEY_ID,
                KEY_NAME, COL_BCWS, COL_BCWP, COL_ACWP, COL_MR, COL_EAC, 
                COL_BAC, COL_CBB }, KEY_NAME + "=?",
                new String[] { pName }, null, null, null, null);
 
System.out.println("cursor ===> " + cursor);
         if (cursor != null)
            cursor.moveToFirst();

System.out.println("cursor.getString(0) ===> " + cursor.getString(0));
System.out.println("cursor.getString(1) ===> " + cursor.getString(1));
System.out.println("cursor.getString(2) ===> " + cursor.getString(2));
System.out.println("cursor.getString(3) ===> " + cursor.getString(3));
System.out.println("cursor.getString(4) ===> " + cursor.getString(4));
System.out.println("cursor.getString(5) ===> " + cursor.getString(5));
System.out.println("cursor.getString(6) ===> " + cursor.getString(6));
System.out.println("cursor.getString(7) ===> " + cursor.getString(7));
System.out.println("cursor.getString(8) ===> " + cursor.getString(8));


         EVMSProject project = new EVMSProject(Integer.parseInt(cursor.getString(0)),
                cursor.getString(1), Float.parseFloat(cursor.getString(2)), Float.parseFloat(cursor.getString(3)),
                Float.parseFloat(cursor.getString(4)), Float.parseFloat(cursor.getString(5)),
                Float.parseFloat(cursor.getString(6)), Float.parseFloat(cursor.getString(7)),
                Float.parseFloat(cursor.getString(8)));
        
   
 System.out.println("project ====> " + project);
    	
        return project;
    }
    
    // Retrieving all Projects
    public List<EVMSProject> getAllProjects() {
    	
        List<EVMSProject> projectList = new ArrayList<EVMSProject>();
        
        // Select All Query
        String selectQuery = "SELECT * FROM " + TABLE_PROJECTS;
 
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
 
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                EVMSProject project = new EVMSProject(Integer.parseInt(cursor.getString(0)),
                    cursor.getString(1), Float.parseFloat(cursor.getString(2)), Float.parseFloat(cursor.getString(3)),
                    Float.parseFloat(cursor.getString(4)), Float.parseFloat(cursor.getString(5)),
                    Float.parseFloat(cursor.getString(6)), Float.parseFloat(cursor.getString(7)),
                    Float.parseFloat(cursor.getString(8)));

                projectList.add(project);
            } while (cursor.moveToNext());
        }
 
        return projectList;
    }
 
    // Updating single project
    public int updateProject(EVMSProject pProject) {
    	
        SQLiteDatabase db = this.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(KEY_NAME, pProject.getName());
        values.put(COL_BCWS, pProject.getBCWS());
        values.put(COL_BCWP, pProject.getBCWP());
        values.put(COL_ACWP, pProject.getACWP());
        values.put(COL_MR, pProject.getMR());
        values.put(COL_EAC, pProject.getEAC());
        values.put(COL_BAC, pProject.getBAC());
        values.put(COL_CBB, pProject.getCBB());
        
        // updating row
        return db.update(TABLE_PROJECTS, values, KEY_ID + " = ?",
                new String[] { String.valueOf(pProject.getID()) });
    }
 
    // Deleting single Project
    public void deleteProject(EVMSProject pProject) {
    	
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_PROJECTS, KEY_ID + " = ?",
                new String[] { String.valueOf(pProject.getID()) });
        
        db.close();
    }
    
    public int deleteProjectsByName(String whereNameEquals) {
        SQLiteDatabase db = this.getWritableDatabase();
        int rowsDeleted = db.delete(TABLE_PROJECTS, KEY_NAME + " like ?",
                new String[] { whereNameEquals });
        
        db.close();
        
        return rowsDeleted;
    }
 
    // Getting Projects Count
    public int getProjectCount() {
    	
        String countQuery = "SELECT  * FROM " + TABLE_PROJECTS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int result = cursor.getCount();
        cursor.close();
 
        // return count
        return result;
    }
 
}